package com.song.EMS.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.song.EMS.exception.ExpressNumberException;

import java.sql.*;
import java.util.List;
import java.util.ResourceBundle;

/**
 * 数据库（Druid）链接
 *
 * @author song
 */
public class DruidUtil {
    /**
     * 自定变量
     */
    private Connection connection = null;
    protected ResultSet resultSet = null;
    protected PreparedStatement pps = null;
    /**
     * 更新删除影响数据的行数
     */
    private int count;

    private static String driverName;
    private static String userName;
    private static String userPassword;
    private static String url;

    /**
     * 创建德鲁伊对象
     */
    private static DruidDataSource druidDataSource = new DruidDataSource();

    /**加载驱动
     *
     */
    static {
        ResourceBundle bundle = ResourceBundle.getBundle("druid");
        driverName = bundle.getString("driverclass");
        userName = bundle.getString("userName");
        url = bundle.getString("url");
        userPassword = bundle.getString("userPass");

        druidDataSource.setDriverClassName(driverName);
        druidDataSource.setUsername(userName);
        druidDataSource.setPassword(userPassword);
        druidDataSource.setUrl(url);
    }

    /**
     * 获取链接
     *
     * @return 链接
     */
    protected Connection getConnection() {
        try {
            connection = druidDataSource.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }

    /**
     * 创建与状态通道
     *
     * @param sql sql语句
     * @return
     */
    protected PreparedStatement getPps(String sql) {

        try {
            //Statement.RETURN_GENERATED_KEYS可用返回新增数据的索引
            pps = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return pps;
    }

    /**
     * sql语句赋值，list为占位符赋值
     *
     * @param list 存sql语句中占位符的值的集合
     */
    protected void setSql(List list) {
        if (list != null && list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                try {
                    pps.setObject(i + 1, list.get(i));
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }

    /**
     * 关闭资源
     */
    protected void close() {
        try {
            if (connection != null) {
                connection.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
            if (pps != null) {
                pps.close();
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 查询数据
     *
     * @param sql  查询的sql语句
     * @param list 占位符值的集合
     * @return 查询的结果
     */
    protected ResultSet select(String sql, List list) {
        try {
            getPps(sql);
            setSql(list);
            resultSet = pps.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        return resultSet;
    }

    /**
     * 更新删除数据
     *
     * @param sql  查询的sql语句
     * @param list 占位符值的集合
     * @return 查询的结果
     */
    protected int update(String sql, List list) throws SQLException {
      /*  try {
            getPps(sql);
            setSql(list);
            count = pps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();

        }
        return count;*/

        getPps(sql);
        setSql(list);
        count = pps.executeUpdate();

        return count;
    }

}
